﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
using System.Security.Principal;
using System.Net.NetworkInformation;
using System.IO;

namespace Worktool.CODE_Chris.SQL_DATA
{
    class SQL
    {
        private SqlConnection connection;
        public static String connectionString;

        public SQL()
        {
            init();
        }

        private void init()
        {
            connectionString = Login.connection;
        }

        public void log(String message, Exception exception, String actualClass)
        {
            //String[] commands;
            //String logType = "NORMAL";

            //if (exception != null)
            //{
            //    logType = "ERROR";
            //    commands = new String[6];
            //    commands[0] = "INSERT INTO TOOL_Log VALUES('" + System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString() + "','" + retrieveLinkerTimestamp().ToString() + "','" + DateTime.Now + "','" + GUI.userName + "','" + getWindowsIdentity() + "','" + getMacAddress() + "','" + actualClass + "','" + message + "_6 (Exception.ToString()): " + exception.ToString() + "','" + logType + "')";
            //    commands[1] = "INSERT INTO TOOL_Log VALUES('" + System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString() + "','" + retrieveLinkerTimestamp().ToString() + "','" + DateTime.Now + "','" + GUI.userName + "','" + getWindowsIdentity() + "','" + getMacAddress() + "','" + actualClass + "','" + message + "_5 (Exception.StackTrace): " + exception.StackTrace + "','" + logType + "')";
            //    commands[2] = "INSERT INTO TOOL_Log VALUES('" + System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString() + "','" + retrieveLinkerTimestamp().ToString() + "','" + DateTime.Now + "','" + GUI.userName + "','" + getWindowsIdentity() + "','" + getMacAddress() + "','" + actualClass + "','" + message + "_4 (Exception.message): " + exception.Message + "','" + logType + "')";
            //    commands[3] = "INSERT INTO TOOL_Log VALUES('" + System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString() + "','" + retrieveLinkerTimestamp().ToString() + "','" + DateTime.Now + "','" + GUI.userName + "','" + getWindowsIdentity() + "','" + getMacAddress() + "','" + actualClass + "','" + message + "_3 (Exception.InnerException): " + exception.InnerException + "','" + logType + "')";
            //    commands[4] = "INSERT INTO TOOL_Log VALUES('" + System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString() + "','" + retrieveLinkerTimestamp().ToString() + "','" + DateTime.Now + "','" + GUI.userName + "','" + getWindowsIdentity() + "','" + getMacAddress() + "','" + actualClass + "','" + message + "_2 (Exception.BaseException): " + exception.GetBaseException() + "','" + logType + "')";
            //    commands[5] = "INSERT INTO TOOL_Log VALUES('" + System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString() + "','" + retrieveLinkerTimestamp().ToString() + "','" + DateTime.Now + "','" + GUI.userName + "','" + getWindowsIdentity() + "','" + getMacAddress() + "','" + actualClass + "','" + message + "_1 (Exception.Type): " + exception.GetType() + "','" + logType + "')";
            //}
            //else
            //{
            //    commands = new String[1];
            //    commands[0] = "INSERT INTO TOOL_Log VALUES('" + System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString() + "','" + retrieveLinkerTimestamp().ToString() + "','" + DateTime.Now + "','" + GUI.userName + "','" + getWindowsIdentity() + "','" + getMacAddress() + "','" + actualClass + "','" + message + "','" + logType + "')";
            //}

            //using (connection = new SqlConnection(connectionString))
            //{
            //    try
            //    {
            //        connection.Open();

            //        for (int a = 0; a < commands.Length; a++)
            //        {
            //            SqlCommand update = new SqlCommand(commands[a], connection);
            //            update.ExecuteNonQuery();
            //        }

            //        connection.Close();
            //    }
            //    catch (SqlException e)
            //    {

            //    }
            //}

            //DEBUG
            //if (message.Contains("Fehler")) MessageBox.Show(message);
        }

        public enum Platform
        {
            Windows,
            Linux,
            Mac
        }

        public static Platform RunningPlatform()
        {
            switch (Environment.OSVersion.Platform)
            {
                case PlatformID.Unix:
                    // Well, there are chances MacOSX is reported as Unix instead of MacOSX.
                    // Instead of platform check, we'll do a feature checks (Mac specific root folders)
                    if (Directory.Exists("/Applications")
                        & Directory.Exists("/System")
                        & Directory.Exists("/Users")
                        & Directory.Exists("/Volumes"))
                        return Platform.Mac;
                    else
                        return Platform.Linux;

                case PlatformID.MacOSX:
                    return Platform.Mac;

                default:
                    return Platform.Windows;
            }
        }

        public DateTime retrieveLinkerTimestamp()
        {
            string filePath = System.Reflection.Assembly.GetCallingAssembly().Location;
            const int c_PeHeaderOffset = 60;
            const int c_LinkerTimestampOffset = 8;
            byte[] b = new byte[2048];
            System.IO.Stream s = null;

            try
            {
                s = new System.IO.FileStream(filePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);
                s.Read(b, 0, 2048);
            }
            finally
            {
                if (s != null)
                {
                    s.Close();
                }
            }

            int i = System.BitConverter.ToInt32(b, c_PeHeaderOffset);
            int secondsSince1970 = System.BitConverter.ToInt32(b, i + c_LinkerTimestampOffset);
            DateTime dt = new DateTime(1970, 1, 1, 0, 0, 0);
            dt = dt.AddSeconds(secondsSince1970);
            dt = dt.AddHours(TimeZone.CurrentTimeZone.GetUtcOffset(dt).Hours);
            return dt;
        }

        private String getWindowsIdentity()
        {
            try
            {
                return WindowsIdentity.GetCurrent().Name;
            }
            catch (Exception e)
            {
                return "Unknown";
            }
        }

        private String getMacAddress()
        {
            const int MIN_MAC_ADDR_LENGTH = 12;
            String macAddress = "";
            long maxSpeed = -1;
            try
            {
                foreach (NetworkInterface nic in NetworkInterface.GetAllNetworkInterfaces())
                {
                    string tempMac = nic.GetPhysicalAddress().ToString();
                    if (nic.Speed > maxSpeed && !String.IsNullOrEmpty(tempMac) && tempMac.Length >= MIN_MAC_ADDR_LENGTH)
                    {
                        maxSpeed = nic.Speed;
                        macAddress = tempMac;
                    }
                }
            }
            catch (Exception e)
            {
                return "Unknown";
            }

            return macAddress;
        }

        public void sendSQL(String command)
        {
            using (connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    SqlCommand update = new SqlCommand(command, connection);

                    update.ExecuteNonQuery();

                    connection.Close();

                }
                catch (SqlException e)
                {
                    log("FEHLER", e, this.ToString() + ":" + System.Reflection.MethodBase.GetCurrentMethod().Name);
                }

            }
        }

        public String[,] receiveSQL(String sqlCommand)
        {
            String[,] temp = new String[0, 0];
            int? amount = 0;
            int count = 0;

            String sqlCount = "SELECT COUNT(*) AS Amount FROM (" + sqlCommand + ")a";

            amount = getAmountSQL(sqlCount);

            using (connection = new SqlConnection(connectionString))
            {
                connection.Open();

                try
                {
                    using (SqlCommand command = new SqlCommand(sqlCommand, connection))
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            if (reader != null)
                            {
                                temp = new String[reader.FieldCount, Convert.ToInt16(amount)];
                                while (reader.Read())
                                {
                                    for (int a = 0; a < reader.FieldCount; a++)
                                    {
                                        temp[a, count] = Convert.ToString(reader[a]);
                                    }

                                    count++;
                                }
                            }
                        }
                    }



                }
                catch (SqlException e)
                {
                    log("FEHLER", e, this.ToString() + ":" + System.Reflection.MethodBase.GetCurrentMethod().Name);
                }
                catch (Exception e)
                {
                    log("FEHLER", e, this.ToString() + ":" + System.Reflection.MethodBase.GetCurrentMethod().Name);
                }
            }

            return temp;
        }

        public String[,] receiveSQL(String sqlCount, String sqlCommand)
        {
            String[,] temp = new String[0, 0];
            int? amount = 0;
            int count = 0;

            amount = getAmountSQL(sqlCount);

            using (connection = new SqlConnection(connectionString))
            {
                connection.Open();

                try
                {
                    using (SqlCommand command = new SqlCommand(sqlCommand, connection))
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            if (reader != null)
                            {
                                temp = new String[reader.FieldCount, Convert.ToInt16(amount)];
                                while (reader.Read())
                                {
                                    for (int a = 0; a < reader.FieldCount; a++)
                                    {
                                        temp[a, count] = Convert.ToString(reader[a]);
                                    }

                                    count++;
                                }
                            }
                        }
                    }



                }
                catch (SqlException e)
                {
                    log("FEHLER", e, this.ToString() + ":" + System.Reflection.MethodBase.GetCurrentMethod().Name);
                }
                catch (Exception e)
                {
                    log("FEHLER", e, this.ToString() + ":" + System.Reflection.MethodBase.GetCurrentMethod().Name);
                }
            }

            return temp;
        }

        public int? getAmountSQL(String sql)
        {
            int? temp = 0;
            using (connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();


                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            if (reader != null)
                            {
                                while (reader.Read())
                                {
                                    temp = reader["Amount"] as int?;
                                }
                            }
                        }
                    }
                    connection.Close();
                }
                catch (SqlException e)
                {
                    log("FEHLER", e, this.ToString() + ":" + System.Reflection.MethodBase.GetCurrentMethod().Name);
                }
                catch (Exception e)
                {
                    log("FEHLER", e, this.ToString() + ":" + System.Reflection.MethodBase.GetCurrentMethod().Name);
                }
            }
            return temp;
        }


        public String getSingleValueSQL(String sql)
        {
            String temp = "";
            using (connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();


                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            if (reader != null)
                            {
                                while (reader.Read())
                                {
                                    temp = reader["Value"] as String;
                                }
                            }
                        }
                    }
                    connection.Close();
                }
                catch (SqlException e)
                {
                    log("FEHLER", e, this.ToString() + ":" + System.Reflection.MethodBase.GetCurrentMethod().Name);
                }
                catch (Exception e)
                {
                    log("FEHLER", e, this.ToString() + ":" + System.Reflection.MethodBase.GetCurrentMethod().Name);
                }
            }
            return temp;
        }

        public String insertNewRow(SqlCommand insCmd)
        {
            int error = 0;
            using (connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    insCmd.Connection = connection;
                    insCmd.ExecuteNonQuery();
                    connection.Close();
                }
                catch (SqlException e)
                {
                    log("FEHLER", e, this.ToString() + ":" + System.Reflection.MethodBase.GetCurrentMethod().Name);
                    error++;
                }
            }

            if (error == 0)
            {
                return insCmd.Parameters["@Identity"].Value.ToString();
            }
            else
            {
                return null;
            }
        }

        public void setConnectionString(String connString)
        {
            connectionString = connString;
        }
    }
}
